CREATE DATABASE [clinica];


CREATE TABLE [dbo].[Medico] (
    [CRM]           VARCHAR (50) NOT NULL,
    [Especialidade] VARCHAR (50) NOT NULL,
    [Nome]          VARCHAR (50) NOT NULL,
    [Turno]         VARCHAR (50) NOT NULL
);

ALTER TABLE [dbo].[Medico]
    ADD CONSTRAINT [PK_Medico] PRIMARY KEY CLUSTERED ([CRM] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);




CREATE TABLE [dbo].[Paciente] (
    [cpf]      VARCHAR (11)  NOT NULL,
    [nome]     VARCHAR (100) NOT NULL,
    [plano]    VARCHAR (50)  NULL,
    [endereco] VARCHAR (50)  NULL,
    [telefone] VARCHAR (50)  NOT NULL,
    [email]    VARCHAR (50)  NULL
);
ALTER TABLE [dbo].[Paciente]
    ADD CONSTRAINT [PK_Paciente] PRIMARY KEY CLUSTERED ([cpf] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);




CREATE TABLE [dbo].[Consulta] (
    [id] [int] NOT NULL IDENTITY(1,1),
    [crm] VARCHAR (50) NOT NULL,
    [cpf] VARCHAR (11) NOT NULL,
    [dataHora] datetime NOT NULL
);

ALTER TABLE [dbo].[Consulta]
    ADD CONSTRAINT [PK_Consulta] PRIMARY KEY CLUSTERED ([id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);



ALTER TABLE [dbo].[Consulta]
    ADD CONSTRAINT [FK_Consulta_Medico] FOREIGN KEY ([crm]) REFERENCES [dbo].[Medico] ([CRM]) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE [dbo].[Consulta]
    ADD CONSTRAINT [FK_Consulta_Paciente] FOREIGN KEY ([cpf]) REFERENCES [dbo].[Paciente] ([cpf]) ON DELETE NO ACTION ON UPDATE NO ACTION;
